<?php
  include dirname(__FILE__)."/../database/SQLgenerator.class.php";

	class Database
	{	
		private $conn;
		private $result;
		private $rowCnt;
    private $SQLgenerator;
	
		public function Database()
		{
			include dirname(__FILE__)."/../config/databaseconfig.inc.php";
			$this->conn = new mysqli($database_server_url, $database_username, $database_password, "MGTdatabase", $database_port);
			if($this->conn->connect_errno !=0)
      {
				die("Could not connect to database");
			}

      $this->SQLgenerator = new SQLgenerator($this->conn);
		}
		
    public function escape_string($string)
    {
      return $this->conn->real_escape_string($string);
    }

		public function doSQL($sql)
		{	
			return ($this->result = $this->conn->query($sql));
		}
		
		public function getRecord()
		{
			$row = $this->result->fetch_object();
			return $row;
		}

    public function getArray($sql)
    {
        $temp_array = array();
        if($this->doSQL($sql))
        {
          while($row = $this->getRecord())
          {
            array_push($temp_array, $row);
          }
        }
        else
        {
          die("SQL error.");
        }
        $this->result->close();
        return $temp_array;
    }

/*
 * CARD
 */

    public function getTotalCardCount($cardid, $ownerid, $collectionid = NULL)
    {
      $cardid = $this->conn->real_escape_string($cardid);
      $ownerid = $this->conn->real_escape_string($ownerid);
      $collectionid = $this->conn->real_escape_string($collectionid);

      $SQL = "SELECT SUM(amount_normal) as total_amount_normal, SUM(amount_foil) as total_amount_foil, 0 as amount_normal, 0 as amount_foil
                                FROM `collection_card`
                                INNER JOIN `Collection` ON `Collection`.`ID` = `collection_card`.`collection_ID`
                                WHERE `collection_card`.`card_ID` = $cardid AND `Collection`.`owner` = $ownerid";

      if(is_numeric($collectionid))
      {
        $SQL .= " UNION
                 SELECT 0 as total_amount_normal, 0 as total_amount_foil, SUM(amount_normal) as amount_normal, SUM(amount_foil) as amount_foil
                 FROM `collection_card`
                 WHERE `collection_card`.`card_ID` = $cardid AND `collection_card`.`collection_ID` = $collectionid";
                 
        $SQL = "SELECT SUM(total_amount_normal) as total_amount_normal, SUM(total_amount_foil) as total_amount_foil, SUM(amount_normal) as amount_normal, SUM(amount_foil) as amount_foil FROM (" . $SQL . ") as total_amount";
      }
      
      $array = $this->getArray($SQL);

      if(empty($array[0]->total_amount_normal))
      {
        $array[0]->total_amount_normal = 0;
      }
      if(empty($array[0]->total_amount_foil))
      {
        $array[0]->total_amount_foil = 0;
      }
      if(empty($array[0]->amount_normal))
      {
        $array[0]->amount_normal = 0;
      }
      if(empty($array[0]->amount_foil))
      {
        $array[0]->amount_foil = 0;
      }
      return $array[0];
    }

/*
 * EXPANSION
 */

    public function getCardsInExpansion($expansionID, $order, $limit)
    {
      $expansionID = $this->conn->real_escape_string($expansionID);
      $order = $this->SQLgenerator->createOrderSQL($order);
      $limit = $this->SQLgenerator->createPageSQL($limit);

      $SQL = "SELECT `Card`.`ID` FROM `Card` WHERE `Card`.`expansion`=\"$expansionID\" $order $limit";
      
      return $this->getArray($SQL);
    }

    public function getExpansion($expansionid)
    {
      $expansionid = $this->conn->real_escape_string($expansionid);
      $limit = $this->SQLgenerator->createPageSQL(array('page' => 0, 'pagelimit' => 1));

      $array = $this->getArray("SELECT `Expansion`.`name`, `Expansion`.`internal_name` FROM `Expansion` WHERE `Expansion`.`ID` = \"$expansionid\" $limit");
      if(!empty($array))
      {
        return $array[0];
      }
      return 0;
    }

    public function getExpansionID($expansionInternalName)
    {
      $expansionInternalName = $this->conn->real_escape_string($expansionInternalName);
      $limit = $this->SQLgenerator->createPageSQL(array('page' => 0, 'pagelimit' => 1));

      $array = $this->getArray("SELECT `Expansion`.`ID` FROM `Expansion` WHERE `Expansion`.`internal_name` = \"$expansionInternalName\" $limit");
      if(!empty($array))
      {
        return $array[0]->ID;
      }
      return 0;
    }

    public function getExpansionOfCard($cardid)
    {
      $cardid = $this->conn->real_escape_string($cardid);
      $limit = $this->SQLgenerator->createPageSQL(array('page' => 0, 'pagelimit' => 1));

      $array = $this->getArray("SELECT `Expansion`.`ID`, `Expansion`.`name`, `Expansion`.`internal_name` FROM `Expansion` INNER JOIN `Card` ON `Expansion`.`ID` = `Card`.`Expansion` WHERE `Card`.`ID` = \"$cardid\" $limit");
      if(!empty($array))
      {
        return $array[0];
      }
      return 0;
    }

    public function getExpansions()
    {
      $order = $this->SQLgenerator->createOrderSQL(array(array('column' => "`Expansion`.`release_date`", 'direction' => "DESC")));

      return $this->getArray("SELECT `ID`, `name`, `internal_name` FROM `Expansion` $order");
    }

    public function getExpansionSize($expansion_internal_name)
    {
      $size = $this->getArray("SELECT count(*) as size FROM `Card` INNER JOIN `Expansion` ON `Card`.`expansion` = `Expansion`.`ID` WHERE `Expansion`.`internal_name`=\"$expansion_internal_name\"");
      return $size[0]->size;
    }

    public function getAmountOfUniqueCardsOwnedInExpansion($expansionid, $owner)
    {
      $owner = $this->conn->real_escape_string($owner);
      $size = $this->getArray("SELECT count(distinct(`ID`)) as size
                               FROM `Card`
                               WHERE (`ID` IN(
                                               SELECT `card_ID`
                                               FROM `collection_card`
                                               INNER JOIN `Collection` ON `collection_card`.`collection_ID` = `Collection`.`ID`
                                               WHERE `Collection`.`owner` = \"$owner\"
                                             )
                                     ) AND `expansion`=\"$expansionid\"");
      return $size[0]->size;
    }

/*
 * COLLECTION
 */

    public function addCollection($name, $ownerid)
    {
      $name = $this->conn->real_escape_string($name);
      $ownerid = $this->conn->real_escape_string($ownerid);

      $array = $this->getArray("SELECT count(*) as size FROM `Collection` WHERE `name`=\"$name\" AND `owner`=\"$ownerid\"");

      if($array[0]->size == 0)
      {
        $SQL = "INSERT INTO `Collection` (`is_deck`, `name`, `owner`) VALUES (0, \"$name\", \"$ownerid\")";
        
        if($this->doSQL($SQL))
        {
          return 1;
        }
        return "An error occured";
      }

      return "Another collection or deck you own already has this name.";
    }

    public function removeCollection($collectionid)
    {
      $collectionid = $this->conn->real_escape_string($collectionid);
      if(isset($_COOKIE["MGTDatabase_user"]) && is_numeric($_COOKIE["MGTDatabase_user"]))
      {
        $owner = $this->conn->real_escape_string($_COOKIE["MGTDatabase_user"]);
        $array = $this->getArray("SELECT count(*) as size FROM `Collection` WHERE `ID`=$collectionid AND `owner`=\"$owner\"");
        if($array[0]->size != 0)
        {        
          $this->doSQL("DELETE FROM collection_card WHERE collection_id=\"$collectionid\"");
          $this->doSQL("DELETE FROM `Collection` WHERE `ID`=\"$collectionid\"");
        }
      }
    }

    public function changeCollection($collectionid, $name = FALSE, $description = FALSE)
    {
      $collectionid = $this->conn->real_escape_string($collectionid);
      $name = $this->conn->real_escape_string($name);
      $description = $this->conn->real_escape_string($description);

      $SQL = 'UPDATE `Collection` SET';
      if($name)
      {
        //check if name is taken
        $owner = $this->conn->real_escape_string($_COOKIE["MGTDatabase_user"]);
        $array = $this->getArray('SELECT count(*) as size FROM `Collection` WHERE `name`="'.$name.'" AND `owner`="'.$owner.'"');
        if($array[0]->size == 0)
        {
          $SQL .=' `name`="'.$name.'" ';
        }
      }
      if($description)
      {
        if($name)
        {
          $SQL .= ',';
        }
        $SQL .=' `description`="'.$description.'" ';
      }
      $SQL .= "WHERE `Collection`.`ID` = $collectionid";

      $this->doSQL($SQL);
    }

    public function getCollections($ownerid)
    {
      $ownerid = $this->conn->real_escape_string($ownerid);
      return $this->getArray("SELECT `Collection`.`ID` FROM `Collection` WHERE `Collection`.`owner`=$ownerid AND `Collection`.`is_deck` = FALSE ORDER BY `Collection`.`Name` ASC");
    }

    public function getCollectionDetails($collectionid)
    {
      $collectionid = $this->conn->real_escape_string($collectionid);
      $details = $this->getArray("SELECT `Collection`.`name`, `Collection`.`description`, `Collection`.`Visibility` FROM `Collection` WHERE `Collection`.`ID` = $collectionid LIMIT 1");
      if(!empty($details[0]))
      {
        return $details[0];
      }
      return NULL;
    }

    public function getCollectionSize($collectionid)
    {
      $collectionid = $this->conn->real_escape_string($collectionid);
      $size = $this->getArray("SELECT SUM(amount_normal) + SUM(amount_foil) as size FROM `collection_card` WHERE `collection_card`.`collection_ID`=\"$collectionid\"");
      if($size[0]->size != NULL)
      {
        return $size[0]->size;
      }
      return 0;
    }

    public function getTotalUniqueCardsInCollection($collectionid)
    {
      $collectionid = $this->conn->real_escape_string($collectionid);
      $size = $this->getArray("SELECT count(*) as size FROM `collection_card` WHERE `collection_card`.`collection_ID`=\"$collectionid\"");
      if(!empty($size[0]->size))
      {
        return $size[0]->size;
      }
      return 0;
    }

    public function getCardsInCollection($collectionid, $order, $limit)
    {
      $collectionid = $this->conn->real_escape_string($collectionid);
      $order = $this->SQLgenerator->createOrderSQL($order);
      $limit = $this->SQLgenerator->createPageSQL($limit);

      return $this->getArray("SELECT `Card`.`ID` FROM `Card` INNER JOIN `collection_card` ON `collection_card`.`card_ID` = `Card`.`ID` INNER JOIN `Expansion` ON `Card`.`expansion` = `Expansion`.`ID` WHERE `collection_card`.`collection_ID` = \"$collectionid\" $order $limit");
    }

    public function getAmountInCollection($cardid, $collection)
    {
      $cardid = $this->conn->real_escape_string($cardid);
      $collection = $this->conn->real_escape_string($collection);
      $limit = $this->SQLgenerator->createPageSQL(array('page' => 0, 'pagelimit' => 1));

      $amount = $this->getArray("SELECT `amount_foil`, `amount_normal` FROM `collection_card` WHERE `card_ID`=\"$cardid\" AND `collection_ID`=\"$collection\" $limit");

      if(!empty($amount))
      {
        return array('normal' => $amount[0]->amount_normal, 'foil' => $amount[0]->amount_foil);
      }
      return array('normal' => 0, 'foil' =>0);
    }


    public function addToCollection($cardid, $collectionid, $foil)
    {
      $cardid = $this->conn->real_escape_string($cardid);
      $collectionid = $this->conn->real_escape_string($collectionid);
      $foil = $this->conn->real_escape_string($foil);
      $limit = $this->SQLgenerator->createPageSQL(array('page' => 0, 'pagelimit' => 1));

      //see if it already exists
      $this->doSQL("SELECT * FROM collection_card WHERE card_ID=\"$cardid\" AND collection_id=\"$collectionid\" $limit");
      if($row = $this->getRecord())
      {
        if($foil == TRUE)
        {
          $this->doSQL("UPDATE collection_card SET amount_foil=amount_foil+1 WHERE card_ID=\"$cardid\" AND collection_id=\"$collectionid\"");
        }
        else
        {
          $this->doSQL("UPDATE collection_card SET amount_normal=amount_normal+1 WHERE card_ID=\"$cardid\" AND collection_id=\"$collectionid\"");
        }
      }
      else
      {
          $this->doSQL("INSERT INTO collection_card VALUES (\"$collectionid\", \"$cardid\", \"".!$foil."\", \"".$foil."\")");
      }
    }

    public function delFromCollection($cardid, $collectionid, $foil)
    {
      $cardid = $this->conn->real_escape_string($cardid);
      $collectionid = $this->conn->real_escape_string($collectionid);
      $foil = $this->conn->real_escape_string($foil);
      //see if it already exists
      $this->doSQL("SELECT * FROM collection_card WHERE card_ID=\"$cardid\" AND collection_id=\"$collectionid\"");
      if($row = $this->getRecord())
      {
        if($foil == TRUE)
        {
          if($row->amount_foil >= 1)
          {
            $this->doSQL("UPDATE collection_card SET amount_foil=amount_foil-1 WHERE card_ID=\"$cardid\" AND collection_id=\"$collectionid\"");
          }
          if($row->amount_normal == 0 && $row->amount_foil == 1 )
          {
            $this->doSQL("DELETE FROM collection_card WHERE card_ID=\"$cardid\" AND collection_id=\"$collectionid\"");
          }
        }
        else
        {
          if($row->amount_normal >= 1)
          {
            $this->doSQL("UPDATE collection_card SET amount_normal=amount_normal-1 WHERE card_ID=\"$cardid\" AND collection_id=\"$collectionid\"");
          }
          if($row->amount_normal == 1 && $row->amount_foil == 0 )
          {
            $this->doSQL("DELETE FROM collection_card WHERE card_ID=\"$cardid\" AND collection_id=\"$collectionid\"");
          }
        }
      }
    }

/*
 * DECK
 */

    public function addDeck($name, $ownerid)
    {
      $ownerid = $this->conn->real_escape_string($ownerid);
      $name = $this->conn->real_escape_string($name);
      $array = $this->getArray("SELECT count(*) as size FROM `Collection` WHERE `name`=\"$name\" AND `owner`=\"$ownerid\"");

      if($array[0]->size == 0)
      {
        $SQL = "INSERT INTO `Collection` (`is_deck`, `name`, `owner`) VALUES (1, \"$name\", \"$ownerid\")";

        if($this->doSQL($SQL))
        {
          return 1;
        }
        return "An error occured";
      }

      return "Another collection or deck you own already has this name.";
    }

    public function removeDeck($deckid)
    {
      $deckid = $this->conn->real_escape_string($deckid);
      if(isset($_COOKIE["MGTDatabase_user"]) && is_numeric($_COOKIE["MGTDatabase_user"]))
      {
        $owner = $this->conn->real_escape_string($_COOKIE["MGTDatabase_user"]);
        $array = $this->getArray("SELECT count(*) as size FROM `Collection` WHERE `ID`=$deckid AND `owner`=\"$owner\"");
        if($array[0]->size != 0)
        {        
          $this->doSQL("DELETE FROM `collection_card` WHERE `collection_ID`=\"$deckid\"");
          $this->doSQL("DELETE FROM `Collection` WHERE `ID`=\"$deckid\"");
        }
      }
    }

    public function getDecks($ownerID)
    {
      $ownerID = $this->conn->real_escape_string($ownerID);
      $order = $this->SQLgenerator->createOrderSQL(array(
                                      array(
                                      'column' => "`Collection`.`name`",
                                      'direction' => "ASC")
                                    ));
      $SQL = "SELECT `ID`, `name`, `description` FROM `Collection` WHERE `owner`=\"$ownerID\" AND `is_deck`=1 $order";
      return $this->getArray($SQL);
    }

    public function getTotalUniqueCardsInDeck($deckid)
    {
      $deckid = $this->conn->real_escape_string($deckid);
      $size = $this->getArray("SELECT count(*) as size FROM `deck_card` WHERE `deck_card`.`deck_ID`=\"$deckid\"");
      if(!empty($size[0]->size))
      {
        return $size[0]->size;
      }
      return 0;
    }

    public function getTotalCardsInDeck($deckid)
    {
      $size = $this->getArray("SELECT (SUM(amount_normal) + SUM(amount_foil)) as size FROM `deck_card` WHERE `deck_card`.`deck_ID`=\"$deckid\"");
      if(!empty($size[0]->size))
      {
        return $size[0]->size;
      }
      return 0;
    }

    public function getCardsInDeck($deckid, $order, $limit)
    {
      $deckid = $this->conn->real_escape_string($deckid);
      $order = $this->SQLgenerator->createOrderSQL($order);
      $limit = $this->SQLgenerator->createPageSQL($limit);

      $SQL = "SELECT `Card`.`ID` FROM `Card` INNER JOIN `collection_card` ON `collection_card`.`card_ID` = `Card`.`ID` INNER JOIN `Expansion` ON `Card`.`expansion` = `Expansion`.`ID` WHERE `collection_card`.`collection_ID` = \"$deckid\" $order $limit";
      return $this->getArray($SQL);
    }

    public function getAmountInDeck($cardid, $deckid)
    {
      $cardid = $this->conn->real_escape_string($cardid);
      $deckid = $this->conn->real_escape_string($deckid);
      $limit = $this->SQLgenerator->createPageSQL(array('page' => 0, 'pagelimit' => 1));

      $amount = $this->getArray("SELECT `amount_foil`, `amount_normal` FROM `deck_card` WHERE `card_ID`=\"$cardid\" AND `deck_ID`=\"$deckid\" $limit");

      if(!empty($amount))
      {
        return array('normal' => $amount[0]->amount_normal, 'foil' => $amount[0]->amount_foil);
      }
      return array('normal' => 0, 'foil' =>0);
    }

    public function addToDeck($cardid, $deckid, $foil)
    {
      $cardid = $this->conn->real_escape_string($cardid);
      $deckid = $this->conn->real_escape_string($deckid);
      $foil = $this->conn->real_escape_string($foil);
      //see if it already exists
      $this->doSQL("SELECT * FROM deck_card WHERE card_ID=\"$cardid\" AND deck_ID=\"$deckid\"");
      if($row = $this->getRecord())
      {
        if($foil == TRUE)
        {
          $this->doSQL("UPDATE deck_card SET amount_foil=amount_foil+1 WHERE card_ID=\"$cardid\" AND deck_ID=\"$deckid\"");
        }
        else
        {
          $this->doSQL("UPDATE deck_card SET amount_normal=amount_normal+1 WHERE card_ID=\"$cardid\" AND deck_ID=\"$deckid\"");
        }

      }
      else
      {
        $this->doSQL("INSERT INTO deck_card VALUES (\"$deckid\", \"$cardid\", \"".!$foil."\", \"".$foil."\")");
      }
    }

    public function delFromDeck($cardid, $deckid, $foil)
    {
      $cardid = $this->conn->real_escape_string($cardid);
      $deckid = $this->conn->real_escape_string($deckid);
      //see if it already exists
      $this->doSQL("SELECT * FROM deck_card WHERE card_ID=\"$cardid\" AND deck_ID=\"$deckid\"");
      if($row = $this->getRecord())
      {
        if($foil == TRUE)
        {
          if($row->amount_foil >= 1)
          {
            $this->doSQL("UPDATE deck_card SET amount_foil=amount_foil-1 WHERE card_ID=\"$cardid\" AND deck_ID=\"$deckid\"");
          }
          if($row->amount_normal == 0 && $row->amount_foil == 1 )
          {
            $this->doSQL("DELETE FROM deck_card WHERE card_ID=\"$cardid\" AND deck_ID=\"$deckid\"");
          }
        }
        else
        {
          if($row->amount_normal >= 1)
          {
            $this->doSQL("UPDATE deck_card SET amount_normal=amount_normal-1 WHERE card_ID=\"$cardid\" AND deck_ID=\"$deckid\"");
          }
          if($row->amount_normal == 1 && $row->amount_foil == 0 )
          {
            $this->doSQL("DELETE FROM deck_card WHERE card_ID=\"$cardid\" AND deck_ID=\"$deckid\"");
          }
        }
      }
    }

/*
 * USER
 */

    public function getUserID($username)
    {
      $username = $this->conn->real_escape_string($username);
      $id = $this->getArray("SELECT `ID` FROM `User` WHERE `name`=\"$username\"");
      if(!empty($id))
      {
        return $id[0]->ID;
      }
      return 0;
    }

/*
 *  Card finder
 */
    public function findCard($cardname, $expansions, $collections, $decks, $collection_deck_operator, $colors, $color_operator, $excluded_colors, $manacost_low, $manacost_high, $type, $rarity, $card_text, $flavor_text, $power, $toughness, $loyalty, $limit)
    {
      $cardname_finder = $this->SQLgenerator->createCardnameSQL($cardname);

      $expansion_finder = $this->SQLgenerator->createExpansionSQL($expansions);
      $color_finder = $this->SQLgenerator->createColorSQL($colors, $color_operator);
      $excluded_color_finder = $this->SQLgenerator->createExcludedColorSQL($excluded_colors);
      $manacost_finder = $this->SQLgenerator->createManaCostLimitSQL($manacost_low, $manacost_high);

      $type_finder = $this-> SQLgenerator->createTypeSQL($type);

      $rarity_finder = $this->SQLgenerator->createRaritySQL($rarity);

      $cardtext_finder = $this->SQLgenerator->createCardtextSQL($card_text);
      $flavortext_finder = $this->SQLgenerator->createFlavortextSQL($flavor_text);

      $collection_finder = $this->SQLgenerator->createCollectionSQL($collections);
      $deck_finder = $this->SQLgenerator->createDeckSQL($decks);

      switch($collection_deck_operator)
      {
        case("XOR"):
        case("AND"):
          break;
        default:
          $collection_deck_operator = "OR";
          break;
      }

      $collection_deck_finder = ($collection_finder === 0 && $deck_finder === 0) ? 1 : "($collection_finder) ".$collection_deck_operator." ($deck_finder)";

      $power_finder = $this -> SQLgenerator -> createCardPowerSQL($power);
      $toughness_finder = $this -> SQLgenerator -> createCardToughnessSQL($toughness);
      $loyalty_finder = $this -> SQLgenerator -> createCardLoyaltySQL($loyalty);
      
      $order = $this->SQLgenerator->createOrderSQL(array(array('column' => "`Expansion`.`Release_date`", 'direction' => "DESC"), array('column' => "`Card`.`name`", 'direction' => "ASC")));
      $limit = $this->SQLgenerator->createPageSQL($limit);

      $SQL = "SELECT DISTINCT `Card_details`.`name` as cardname,
                              `Expansion`.`name` as expansionname,
                              `Expansion`.`internal_name` as expansioninternalname,
                              `Expansion`.`ID` as expansionid,
                              `Card_details`.`Mana_Cost`,
                              `Card_details`.`Types`
              FROM `Card`
              INNER JOIN `Expansion` ON `Card`.`expansion` = `Expansion`.`ID`
              INNER JOIN `Card_details` ON `Card`.`ID` = `Card_details`.`card_ID`
              INNER JOIN `card_details_text` ON `Card`.`ID` = `card_details_text`.`card_ID`
              INNER JOIN `Card_text` ON `Card_text`.`card_text_ID` = `card_details_text`.`card_text_ID`
              WHERE ($cardname_finder)
                AND ($expansion_finder)
                AND ($collection_deck_finder)
                AND ($color_finder)
                AND ($excluded_color_finder)
                AND ($manacost_finder)
                AND ($type_finder)
                AND ($rarity_finder)
                AND ($cardtext_finder)
                AND ($flavortext_finder)
                AND ($power_finder)
                AND ($toughness_finder)
                AND ($loyalty_finder)
              $order
              $limit";
      return $this->getArray($SQL);
    }
	}

$db = new Database();
?>
